package com.app.micaihu.db;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.app.micaihu.app.AppApplication;

/**
 * 新闻相关数据库
 * Created by linkai on 2016/7/20.
 */
public class NewsDBSqlHelper extends SQLiteOpenHelper {

    public static final String DB_NAME = "micai_news.db";//数据库
    //    public static final int DB_VERSION = 1;//版本号
//    public static final int DB_VERSION = 2;//版本号1.0.3 --增加 搜索库的表
//    public static final int DB_VERSION = 3;//版本号-2.0.1--增加 新闻详情的点赞表
//    public static final int DB_VERSION = 4;//版本号-2.0.2--修改点赞表,加入是否赞踩的字段
//    public static final int DB_VERSION = 5;//版本号-2.1.0--增加游戏下载表
    public static final int DB_VERSION = 7;//版本号-2.3.5.1 --增加浏览历史表
    public static final String NEWS_CHANNEL = "news_channel";//新闻栏目表
    public static final String COMMENT_ZAN = "comment_zan";//评论点赞表
    public static final String NEWS_SEE = "news_see";//看过新闻的表
    public static final String COMMENT_REPORT = "comment_report";//举报过的评论表
    public static final String NEWS_SAVE = "news_save";//收藏的新闻表
    public static final String HISTORY_RECORDS = "search_records";//搜索历史

    //2.0.1新增字段--小编推荐,未登录的点赞
    public static final String EDITOR_RECOMMEND = "editor_recommend";//小编推荐的 新闻
    public static final String NEWS_ZAN = "news_zan";//未登录时候的点赞

    //2.1.0版本增加游戏下载表
    public static final String GAME_DOWNLOAD = "game_download";//游戏下载表

    //2.3.5.1 浏览历史
    public static final String BROWSE_HISTORY = "browse_history";//浏览历史表
    public static final String GAME_STATE = "game_state";//记录 下载成功的游戏

    public NewsDBSqlHelper() {
        super(AppApplication.getInstance(), DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建新闻栏目表
        String sql = "create table if not exists " + NEWS_CHANNEL +
                "(_id INTEGER PRIMARY KEY AUTOINCREMENT, id VARCHAR(10) , name TEXT " +
                ", orderid INTEGER , selected SELECTED,type VARCHAR(10))";
        db.execSQL(sql);

        //创建评论点赞
        sql = "create table if not exists " + COMMENT_ZAN
                + "(_id integer primary key)";
        db.execSQL(sql);
        //创建看过新闻
        sql = "create table if not exists " + NEWS_SEE
                + "(_id integer primary key)";
        db.execSQL(sql);
        //创建举报评论表
        sql = "create table if not exists " + COMMENT_REPORT
                + "(_id integer primary key)";
        db.execSQL(sql);
        //创建新闻收藏表
        sql = "create table if not exists "
                + NEWS_SAVE
                + "(_id TEXT primary key"
                + ",articleAttr varchar(10)"
                + ",articleMark varchar(10)"
                + ",picNum varchar(10)"
                + ",articleTime TEXT"
                + ",articleTitle TEXT"
                + ",articleType varchar(10)"
                + ",commentNum varchar(10)"
                + ",uiType varchar(10)"
                + ",praiseNum varchar(10)"
                + ",categoryId varchar(10)"
                + ",articleThumb TEXT"
                + ",saveTime varchar(10));";

        db.execSQL(sql);
        //创建搜索历史表
        sql = "create table if not exists " + HISTORY_RECORDS + "(id integer primary key autoincrement,name varchar(15))";
        db.execSQL(sql);

        //2.0.1版本新增表--存放小编特荐 ---2.0.2废除 ，启用新表
        sql = "create table if not exists " + EDITOR_RECOMMEND + "(exactTime varchar(15) primary key,newsList text)";
        db.execSQL(sql);


        //2.0.1版本新增表---新闻详情的点赞
        sql = "create table if not exists " + NEWS_ZAN + "(_id integer primary key)";
        db.execSQL(sql);

        //2.0.2 新闻点赞表加一字段
        if (!checkColumnExists2(db, NEWS_ZAN, "type")) {
            sql = "ALTER TABLE " + NEWS_ZAN + " ADD type VARCHAR(1);";
            db.execSQL(sql);
        }
        //2.1.0版本增加游戏下载表
        sql = "create table if not exists " + GAME_DOWNLOAD
                + "(_id TEXT primary key,name varchar(20),desc varchar(20),url TEXT,logo TEXT,packname TEXT,path TEXT,size varchar(20))";
        db.execSQL(sql);

        //2.3.5.1 浏览历史
        sql = "create table if not exists " + BROWSE_HISTORY + "(id integer primary key autoincrement,userId TEXT,history TEXT)";
        db.execSQL(sql);

        sql = "create table if not exists " + GAME_STATE + "(id integer primary key autoincrement,gameId TEXT,state TEXT)";
        db.execSQL(sql);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onCreate(db);
    }

    /**
     * 检查表中某列是否存在
     *
     * @param db
     * @param tableName  表名
     * @param columnName 列名
     * @return
     */
    private boolean checkColumnExists2(SQLiteDatabase db, String tableName,
                                       String columnName) {
        boolean result = false;
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("select * from sqlite_master where name = ? and sql like ?"
                    , new String[]{tableName, "%" + columnName + "%"});
            result = null != cursor && cursor.moveToFirst();
        } catch (Exception e) {

        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return result;
    }

}
